use crate::fairing::record::RecordDb;
use network_scanner_server_model::{
    RecordIpAddrEntry, RecordItemEntry, RecordType, RecordTypeEntry, RecordTypeId,
};
use rocket_db_pools::sqlx::{self, sqlite::SqliteRow, Row, Sqlite, Transaction};
use std::{collections::HashMap, sync::Arc};
use tokio::sync::RwLock;

pub async fn add_type<'a>(
    db: &RecordDb,
    r#type: RecordTypeEntry,
) -> Result<RecordTypeId, sqlx::Error> {
    let mut tx = db.begin().await?;

    sqlx::query(
        r#"
            INSERT INTO type
            (name, display_name, ip_count, port_count, str_count, num_count) VALUES (?, ?, ?, ?, ?, ?)
        "#,
    )
    .bind(&r#type.name)
    .bind(&r#type.display_name)
    .bind(r#type.ip_addr.len() as u32)
    .bind(r#type.port.len() as u32)
    .bind(r#type.str.len() as u32)
    .bind(r#type.num.len() as u32)
    .execute(&mut *tx)
    .await?;

    let id: RecordTypeId = RecordTypeId(
        sqlx::query("SELECT LAST_INSERT_ROWID()")
            .map(|row: SqliteRow| row.get(0))
            .fetch_one(&mut *tx)
            .await?,
    );

    for (index, entry) in r#type.ip_addr.iter().enumerate() {
        sqlx::query(
            r#"
                INSERT INTO ip
                (type_id, name, display_name, geoip, type_index) VALUES (?, ?, ?, ?, ?)
            "#,
        )
        .bind(id.0)
        .bind(&entry.base.name)
        .bind(&entry.base.display_name)
        .bind(entry.geoip)
        .bind(index as u32)
        .execute(&mut *tx)
        .await?;
    }

    for (index, entry) in r#type.port.iter().enumerate() {
        sqlx::query(
            r#"
                INSERT INTO port
                (type_id, name, display_name, type_index) VALUES (?, ?, ?, ?)
            "#,
        )
        .bind(id.0)
        .bind(&entry.name)
        .bind(&entry.display_name)
        .bind(index as u32)
        .execute(&mut *tx)
        .await?;
    }

    for (index, entry) in r#type.str.iter().enumerate() {
        sqlx::query(
            r#"
                INSERT INTO str
                (type_id, name, display_name, type_index) VALUES (?, ?, ?, ?)
            "#,
        )
        .bind(id.0)
        .bind(&entry.name)
        .bind(&entry.display_name)
        .bind(index as u32)
        .execute(&mut *tx)
        .await?;
    }

    for (index, entry) in r#type.num.iter().enumerate() {
        sqlx::query(
            r#"
                INSERT INTO num
                (type_id, name, display_name, type_index) VALUES (?, ?, ?, ?)
            "#,
        )
        .bind(id.0)
        .bind(&entry.name)
        .bind(&entry.display_name)
        .bind(index as u32)
        .execute(&mut *tx)
        .await?;
    }

    let ip_names = r#type
        .ip_addr
        .iter()
        .map(|entry| format!("ip_{} BLOB NOT NULL", entry.base.name))
        .collect::<Vec<String>>();
    let port_names = r#type
        .port
        .iter()
        .map(|entry| format!("port_{} INTEGER NOT NULL", entry.name))
        .collect::<Vec<String>>();
    let str_names = r#type
        .str
        .iter()
        .map(|entry| format!("str_{} TEXT NOT NULL", entry.name))
        .collect::<Vec<String>>();
    let num_names = r#type
        .num
        .iter()
        .map(|entry| format!("num_{} INTEGER NOT NULL", entry.name))
        .collect::<Vec<String>>();
    let statement = format!(
        r#"
            CREATE TABLE IF NOT EXISTS record_{} (
                id INTEGER PRIMARY KEY,
                {},
                FOREIGN KEY(id) REFERENCES record(id)
            )
        )"#,
        r#type.name,
        [ip_names, port_names, str_names, num_names]
            .concat()
            .join(",")
    );
    sqlx::query(&statement).execute(&mut *tx).await?;

    tx.commit().await?;

    let mut cache = TYPE_CACHE.write().await;
    let cache = cache.as_mut().expect("Cache is not initialized");

    cache.insert(
        id,
        Arc::new(TypeCache::build_from(RecordType { id, entry: r#type })),
    );

    let mut list_all_cache = LIST_ALL_CACHE.write().await;
    *list_all_cache = Some(Arc::new(generate_list_all(
        cache.values().map(|item| &item.r#type),
    )));

    Ok(id)
}

pub trait RecordTypeExt: Sized {
    async fn get_by_name(name: &str) -> Option<Self>;
    async fn get_all_types_id_uncached<'a>(
        db: &mut Transaction<'a, Sqlite>,
    ) -> Result<Vec<RecordTypeId>, sqlx::Error>;
    async fn get_type_uncached<'a>(
        self,
        db: &mut Transaction<'a, Sqlite>,
    ) -> Result<Option<RecordType>, sqlx::Error>;
}

impl RecordTypeExt for RecordTypeId {
    async fn get_by_name(name: &str) -> Option<Self> {
        let cache = TYPE_CACHE.read().await;
        let cache = cache.as_ref().expect("Cache is not initialized");
        cache
            .iter()
            .find(|item| item.1.r#type.entry.name == name)
            .map(|item| *item.0)
    }

    async fn get_all_types_id_uncached<'a>(
        db: &mut Transaction<'a, Sqlite>,
    ) -> Result<Vec<RecordTypeId>, sqlx::Error> {
        sqlx::query("SELECT id FROM record")
            .try_map(|row: SqliteRow| Ok(RecordTypeId(row.try_get(0)?)))
            .fetch_all(&mut **db)
            .await
    }

    async fn get_type_uncached<'a>(
        self,
        db: &mut Transaction<'a, Sqlite>,
    ) -> Result<Option<RecordType>, sqlx::Error> {
        #[derive(Clone)]
        struct TypeRow {
            name: String,
            display_name: String,
            ip_count: u32,
            port_count: u32,
            str_count: u32,
            num_count: u32,
        }
        #[derive(Clone)]
        struct IpRow {
            entry: RecordIpAddrEntry,
            type_index: u32,
        }
        #[derive(Clone)]
        struct ItemRow {
            entry: RecordItemEntry,
            type_index: u32,
        }
        enum EntryRow {
            Type(TypeRow),
            Ip(IpRow),
            Port(ItemRow),
            Str(ItemRow),
            Num(ItemRow),
        }

        let rows: Vec<EntryRow> = sqlx::query(
            r#"
                SELECT 'type' AS type, name, display_name, ip_count, port_count, str_count, num_count
                FROM type
                WHERE id = 1
                UNION SELECT 'ip' AS type, name, display_name, type_index
                FROM ip
                WHERE type_id = 1
                UNION SELECT 'port' AS type, name, display_name, type_index
                FROM port
                WHERE type_id = 1
                UNION SELECT 'str' AS type, name, display_name, type_index
                FROM str
                WHERE type_id = 1
                UNION SELECT 'num' AS type, name, display_name, type_index
                FROM num
                WHERE type_id = 1
            "#,
        )
        .bind(self.0)
        .try_map(|row: SqliteRow| {
            let r#type: String = row.try_get("type")?;
            Ok(match r#type.as_str() {
                "type" => {
                    let name: String = row.try_get("name")?;
                    let display_name: String = row.try_get("display_name")?;
                    let ip_count: u32 = row.try_get("ip_count")?;
                    let port_count: u32 = row.try_get("port_count")?;
                    let str_count: u32 = row.try_get("str_count")?;
                    let num_count: u32 = row.try_get("num_count")?;
                    EntryRow::Type(TypeRow {
                        name,
                        display_name,
                        ip_count,
                        port_count,
                        str_count,
                        num_count,
                    })
                },
                "ip" => {
                    let name: String = row.try_get("name")?;
                    let display_name: String = row.try_get("display_name")?;
                    let type_index: u32 = row.try_get("type_index")?;
                    let geoip: bool = row.try_get("geoip")?;
                    EntryRow::Ip(IpRow {
                        entry: RecordIpAddrEntry { base: RecordItemEntry { name, display_name }, geoip },
                        type_index,
                    })
                },
                "port" => {
                    let name: String = row.try_get("name")?;
                    let display_name: String = row.try_get("display_name")?;
                    let type_index: u32 = row.try_get("type_index")?;
                    EntryRow::Port(ItemRow {
                        entry: RecordItemEntry { name, display_name },
                        type_index,
                    })
                },
                "str" => {
                    let name: String = row.try_get("name")?;
                    let display_name: String = row.try_get("display_name")?;
                    let type_index: u32 = row.try_get("type_index")?;
                    EntryRow::Str(ItemRow {
                        entry: RecordItemEntry { name, display_name },
                        type_index,
                    })
                },
                "num" => {
                    let name: String = row.try_get("name")?;
                    let display_name: String = row.try_get("display_name")?;
                    let type_index: u32 = row.try_get("type_index")?;
                    EntryRow::Num(ItemRow {
                        entry: RecordItemEntry { name, display_name },
                        type_index,
                    })
                },
                _ => panic!("No such type")
            })
        })
        .fetch_all(&mut **db)
        .await?;

        let type_row = rows.iter().find(|row| matches!(row, EntryRow::Type(_)));
        let type_row = match type_row {
            Some(EntryRow::Type(row)) => row.clone(),
            _ => return Ok(None),
        };

        let mut ip_addr_entries: Vec<Option<RecordIpAddrEntry>> =
            vec![None; type_row.ip_count as usize];
        let mut port_entries: Vec<Option<RecordItemEntry>> =
            vec![None; type_row.port_count as usize];
        let mut str_entries: Vec<Option<RecordItemEntry>> = vec![None; type_row.str_count as usize];
        let mut num_entries: Vec<Option<RecordItemEntry>> = vec![None; type_row.num_count as usize];

        for row in rows {
            match row {
                EntryRow::Type(_) => {}
                EntryRow::Ip(ip) => {
                    ip_addr_entries[ip.type_index as usize] = Some(ip.entry);
                }
                EntryRow::Port(port) => {
                    port_entries[port.type_index as usize] = Some(port.entry);
                }
                EntryRow::Str(str) => {
                    str_entries[str.type_index as usize] = Some(str.entry);
                }
                EntryRow::Num(num) => num_entries[num.type_index as usize] = Some(num.entry),
            }
        }

        Ok(Some(RecordType {
            id: self,
            entry: RecordTypeEntry {
                name: type_row.name.clone(),
                display_name: type_row.display_name.clone(),
                ip_addr: ip_addr_entries.into_iter().flatten().collect(),
                port: port_entries.into_iter().flatten().collect(),
                str: str_entries.into_iter().flatten().collect(),
                num: num_entries.into_iter().flatten().collect(),
            },
        }))
    }
}

pub struct TypeCache {
    pub r#type: RecordType,
    pub insert_type: String,
    pub list: String,
    pub count: String,
}

impl TypeCache {
    fn build_from(r#type: RecordType) -> Self {
        let insert_type = generate_insert_type(&r#type);
        let list = generate_list(&r#type);
        let count = generate_count(&r#type);

        TypeCache {
            r#type,
            insert_type,
            list,
            count,
        }
    }
}

pub static TYPE_CACHE: RwLock<Option<HashMap<RecordTypeId, Arc<TypeCache>>>> =
    RwLock::const_new(None);
pub static LIST_ALL_CACHE: RwLock<Option<Arc<String>>> = RwLock::const_new(None);

pub async fn initialize_cache<'a>(db: &mut Transaction<'a, Sqlite>) -> Result<(), sqlx::Error> {
    let mut cache = TYPE_CACHE.write().await;
    if cache.is_some() {
        panic!("Cache is already initialized");
    }

    let mut cache_map = HashMap::new();
    let mut types = Vec::new();

    for id in RecordTypeId::get_all_types_id_uncached(db).await? {
        let r#type = id
            .get_type_uncached(db)
            .await?
            .expect("Type have no type data");
        types.push(r#type);
    }

    let mut list_all_cache = LIST_ALL_CACHE.write().await;
    *list_all_cache = Some(Arc::new(generate_list_all(types.iter())));

    for r#type in types {
        cache_map.insert(r#type.id, Arc::new(TypeCache::build_from(r#type)));
    }
    *cache = Some(cache_map);

    Ok(())
}

pub async fn get_all_types() -> Vec<RecordType> {
    let cache = TYPE_CACHE.read().await;
    let cache = cache.as_ref().expect("Cache is not initialized");
    cache
        .iter()
        .map(|(type_id, cache_item)| RecordType {
            id: *type_id,
            entry: cache_item.r#type.entry.clone(),
        })
        .collect()
}

fn generate_insert_type(r#type: &RecordType) -> String {
    let entry = &r#type.entry;
    let args_len = entry.ip_addr.len() + entry.port.len() + entry.str.len() + entry.num.len();
    format!(
        r#"
            INSERT INTO record_{} (id, {}, {}, {}, {})
            VALUES ({} ?);
        "#,
        entry.name,
        entry
            .ip_addr
            .iter()
            .map(|entry| format!("ip_{}", entry.base.name))
            .collect::<Vec<String>>()
            .join(", "),
        entry
            .port
            .iter()
            .map(|entry| format!("port_{}", entry.name))
            .collect::<Vec<String>>()
            .join(", "),
        entry
            .str
            .iter()
            .map(|entry| format!("str_{}", entry.name))
            .collect::<Vec<String>>()
            .join(", "),
        entry
            .num
            .iter()
            .map(|entry| format!("num_{}", entry.name))
            .collect::<Vec<String>>()
            .join(", "),
        "?, ".repeat(args_len)
    )
}

fn generate_list(r#type: &RecordType) -> String {
    let entry = &r#type.entry;
    format!(
        r#"
            SELECT * FROM record_{}
            LIMIT ? OFFSET ?
        "#,
        entry.name
    )
}

fn generate_count(r#type: &RecordType) -> String {
    let entry = &r#type.entry;
    format!(
        r#"
            SELECT COUNT(id) FROM record_{}
        "#,
        entry.name
    )
}

fn generate_list_all<'a>(types: impl Iterator<Item = &'a RecordType>) -> String {
    format!(
        r#"
            SELECT *
            FROM (SELECT id, timestamp, type FROM main ORDER BY id LIMIT ? OFFSET ?) AS main
            {}
        "#,
        types
            .map(|r#type| {
                format!(
                    "LEFT JOIN {} ON main.id = {}.id",
                    r#type.entry.name, r#type.entry.name
                )
            })
            .collect::<Vec<String>>()
            .join("")
    )
}
